import baostock as bs
import pandas as pd
#数据库依赖包 安装用命令pip install  pymysql /pip install MySQLdb（Python2）
import pymysql
import datetime
import requests
import time
import sys
from sqlalchemy import create_engine

def getIndex(code):
    # 登陆系统
    lg = bs.login()
    # 显示登陆返回信息
    #print('login respond error_code:'+lg.error_code)
    #print('login respond  error_msg:'+lg.error_msg)

    # 获取指数(综合指数、规模指数、一级行业指数、二级行业指数、策略指数、成长指数、价值指数、主题指数)K线数据
    # 综合指数，例如：sh.000001 上证指数，sz.399106 深证综指 等；
    # 规模指数，例如：sh.000016 上证50，sh.000300 沪深300，sh.000905 中证500，sz.399001 深证成指等；
    # 一级行业指数，例如：sh.000037 上证医药，sz.399433 国证交运 等；
    # 二级行业指数，例如：sh.000952 300地产，sz.399951 300银行 等；
    # 策略指数，例如：sh.000050 50等权，sh.000982 500等权 等；
    # 成长指数，例如：sz.399376 小盘成长 等；
    # 价值指数，例如：sh.000029 180价值 等；
    # 主题指数，例如：sh.000015 红利指数，sh.000063 上证周期 等；
    #数据类型，默认为d，日k线；d=日k线、w=周、m=月、5=5分钟、15=15分钟、30=30分钟、60=60分钟k线数据，不区分大小写；
    # 详细指标参数，参见“历史行情指标参数”章节 start_date为空时取2015-01-01； end为空时取最近一个交易日；, end_date='2020-06-30'
    rs = bs.query_history_k_data_plus(code,
        "date,code,open,high,low,close,preclose,volume,amount,pctChg",
        start_date='2018-01-01', frequency="d")
    #print('query_history_k_data_plus respond error_code:'+rs.error_code)
    #print('query_history_k_data_plus respond  error_msg:'+rs.error_msg)

    # 打印结果集
    data_list = []
    while (rs.error_code == '0') & rs.next():
        # 获取一条记录，将记录合并在一起
        data_list.append(rs.get_row_data())
    result = pd.DataFrame(data_list, columns=rs.fields)
    # 结果集输出到csv文件
    result.to_csv("D:/data/index/"+code+".csv", index=False)
    #print(result)
    # 登出系统
    bs.logout()
#step1 获取code信息

def indexSaveCSVtoDB(code):
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select max(nowdate) from stock_index_daily d where d.code=%s'
    cursor.execute(sql, code)
    dates = cursor.fetchone()
    if (dates[0] == None):
        mdate = '1990-01-01'
    else:
        mdate = dates[0]
    db.commit()
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    path = 'd:/data/index/' + code + '.csv'
    df = pd.read_csv(path)
    df = df[df.date > str(mdate)]
    df['code'] = code
    df['nowdate'] = df['date']
    df['nowprice'] = round(df['close'],4)
    df['nowrate'] = round(df['pctChg'],4)
    dbs = df[['code', 'nowdate', 'nowprice', 'nowrate']]
    engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'),encoding='utf-8')
    dbs.to_sql(name='stock_index_daily', con=engine, if_exists='append', index=False)

def getCode(type='指数'):
    # step1获取数据(从数据库中获取)
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    # 要执行的sql
    if(type=='指数'):
        sql ="select distinct code from stock_index where type='指数'"
    else:
        sql = "select distinct code from stock_index where type in ('基金','自选' )"
    cursor.execute(sql)
    info = cursor.fetchall()
    codelist = []
    for code in info:
        codelist.append(code[0])
    #    print(len(codelist))
    #   print(codelist)#第一个是数据的条数,
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    return codelist

def getDataFund(code):
    #print('getDataFund')
        r0 = requests.get('http://fund.eastmoney.com/pingzhongdata/' + code + '.js')
        ori = r0.text
        #  print(code,'netinfo=',ori)
        path = 'd:/data/fund/' + code + '.txt'
        with  open(path, "w+", encoding='utf-8')  as fo:
            fo.write(ori)
        fo.close()

#step2.2处理成csv文件
def createCSV(code):
        #print('createCSV start!!!'+code)
        f = open('d:/data/fund/' + code + '.txt', encoding='utf-8')
        content = f.read()  # 使用loads()方法，需要先读文件
        f.close()
        line = content.split("Data_netWorthTrend =")[1].split(";/*累计净值走势*/var Data_ACWorthTrend")[0]
        unitList = list(eval(line))
        # 获取累计净值 [时间，累计净值]
        line = content.split(";/*累计净值走势*/var Data_ACWorthTrend =")[1].split(";/*累计收益率走势*/var Data_grandTotal =")[0]
        sumList = list(eval(line))
        # 获取同类排名百分比
        line = content.split(";/*同类排名百分比*/var Data_rateInSimilarPersent=")[1].split(";/*规模变动 mom-较上期环比*/var")[0]
        perList = list(eval(line))
        # print(len(perList),"----", formatTime(perList[0][0]),perList[0][1])
        path = 'd:/data/fund/' + code + '.csv'
        #    print(unitList[0])
        with  open(path, "w")  as fo:
            fo.write('时间,单位净值,每日涨跌,累计净值\n')
            for i in range(0, len(unitList)):  # len(unitList)
                fo.write(formatTime(unitList[i]['x']) + "," + str(unitList[i]['y']) + "," + str(
                    unitList[i]['equityReturn']) + "," + str(sumList[i][1]) + "\n")
        fo.close()
        fundbasic = pd.read_csv(path, encoding='gbk')
        perdf = pd.DataFrame(perList, columns=['时间', '同类排名百分比'])
        perdf['时间'] = perdf['时间'].apply(lambda x: formatTime(x))
        # https://blog.csdn.net/qq_41664845/article/details/80047109
        df = pd.merge(fundbasic, perdf, on='时间', how='outer')  # 取并集
        df = df.set_index('时间')
        df.to_csv(path)
        return df


def fundSaveCSVtoDB(code):
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select max(nowdate) from stock_index_daily d where d.code=%s'
    cursor.execute(sql, code)
    dates = cursor.fetchone()
    if (dates[0] == None):
        mdate = '1990-01-01'
    else:
        mdate = dates[0]
    db.commit()
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    path = 'd:/data/fund/' + code + '.csv'
    df = pd.read_csv(path)
    df = df[df.时间 > str(mdate)]
    df['code'] = code
    df['nowdate'] = df['时间']
    df['nowprice'] = df['单位净值']
    df['nowrate'] = df['每日涨跌']
    dbs = df[['code', 'nowdate', 'nowprice', 'nowrate']]
    engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    dbs.to_sql(name='stock_index_daily', con=engine, if_exists='append', index=False)

#---工具类---#
def formatTime(oristr):
    ori = float(oristr) / 1000
    time_tuple = time.localtime(ori)
    t1 = time.strftime("%Y-%m-%d", time_tuple)
    return str(t1)
#获取前1天的时间 "%Y-%m-%d"
def getTime(mytime,num,format):
    stdtime = datetime.datetime.strptime(mytime, format)
    newtime = (stdtime + datetime.timedelta(days=num)).strftime(format)
    return newtime

def main():
    codelist=getCode('指数')
    for code in codelist:
        print('指数代码:',code)
        getIndex(code)
        indexSaveCSVtoDB(code)
    codelist=getCode('基金')
    for code in codelist:
        print('基金代码:',code)
        getDataFund(code)
        createCSV(code)
        fundSaveCSVtoDB(code)
def test():
    indexSaveCSVtoDB("sh.000001")
    #fundSaveCSVtoDB("001938")
main()